﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class DonthuocDAL : BaseDAL, UTL.IBaseDAL 
    {
        public int Count()
        {
            throw new NotImplementedException();
        }
        //public Boolean deleteobj(object obj)
        //{
        //    throw new NotImplementedException();
        //}

        public DataTable Select()
        {
            string sql = @" SELECT *  FROM donthuoc a join phieukham b on a.sttphieu = b.sttphieu and a.ngaykham=b.ngaykham ";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM donthuoc WHERE madonthuoc = '{0}'";
            sql = string.Format(sql,id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (donthuoc)obj;
            string sql = @"set dateformat mdy; INSERT INTO donthuoc(madonthuoc,ngaykham,sttphieu) VALUES('{0}','{1}','{2}') ";
            sql = string.Format(sql,o.madonthuoc,o.ngaykham,o.sttphieu);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (donthuoc)obj;
            string sql = @"set dateformat mdy; UPDATE donthuoc SET ngaykham='{0}', sttphieu='{1}' WHERE madonthuoc='{2}' ";
            sql = string.Format(sql, o.ngaykham, o.sttphieu, o.madonthuoc);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_sttphieu()
        {
            string sql = @"SELECT sttphieu as idstt,ngaykham as idngay  FROM phieukham";
            return ExecuteQuery(sql);
        }

        /// <summary>
        /// lay ...
        /// </summary>
        /// <returns></returns>
        public string Select_max()
        {
            string sql = @"SELECT max(substring(madonthuoc,3,LEN(madonthuoc)-2)) as id FROM donthuoc";
            DataTable tb= ExecuteQuery(sql);
            
            return tb.Rows.Count>0? tb.Rows[0]["id"]+"":"0";
        }

        public DataTable select_report(string id)

        {
            string sql = @"select a.MADONTHUOC,a.NGAYKHAM,a.STTPHIEU,b.SOLUONG,c.TENTHUOC,d.DONGIA,e.DENVITINH,(b.soluong*d.dongia) as thanhtien
                        from donthuoc a join COTHUOC b on a.MADONTHUOC=b.MADONTHUOC join THUOC c on b.MATHUOC=c.MATHUOC join DONGIATHUOC d on c.MATHUOC=d.MATHUOC join DONVITINH e on c.MADVT=e.MADVT
                        where a.madonthuoc='{0}'";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }


        public bool deleteobj(object obj)
        {
            throw new NotImplementedException();
        }
    }
}
